import sqlite3
import os
import pandas as pd

def dw_result():
    db_path: str = "db/user.db"
    path = "data/ods" #文件夹目录
    files= os.listdir(path) #得到文件夹下的所有文件名称
    filenames = []
    lens = []
    datas = pd.DataFrame()
    print(datas)
    for file in files:  # 遍历文件夹
        if not os.path.isdir(file):  # 判断是否是文件夹，不是文件夹才打开
            f = open(path + "/" + file);  # 打开文件
            data = pd.read_csv(path + "/" + file)
            data = data.dropna(subset=['Result'])  # 删除缺失值
            data = data[data['Result'] != 0]  # 删除0值
            datas = pd.concat([datas, data])

    print(datas.columns)
    datas = datas.rename(columns={'Processor':'processor'})
    datas = datas.rename(columns={'Result':'result'})
    datas = datas.rename(columns={'Company':'company'})
    datas = datas.rename(columns={'System':'system_name'})

    print(datas.columns)


    datas1 = datas.sort_values(['processor', 'result'], ascending=[1, 1])
    cpu_group = datas1.groupby(['processor']).head(1)
    cpu_group = cpu_group[["processor", "result", "company", "system_name"]]

    datas2 = datas.sort_values(['company', 'result'], ascending=[1, 1])
    com_group = datas2.groupby(['company']).head(1)
    com_group = cpu_group[["company", "result","processor", "system_name"]]

    conn = sqlite3.connect(db_path)

    c = conn.cursor()
    # print(data)
    with open(r"dw/script/create_result_template", "r", True, 'UTF-8') as f:
        sql = f.read().format("processor", "processor", "processor", "company")
        c.executescript(sql)
    cpu_group.to_sql(f"result_{'processor'}", conn, if_exists="append", index=False)
    conn.commit()
    c.close()

    c = conn.cursor()
    # print(data)
    with open(r"dw/script/create_result_template", "r", True, 'UTF-8') as f:
        sql = f.read().format("company", "company", "company", "processor")
        c.executescript(sql)
    com_group.to_sql(f"result_{'company'}", conn, if_exists="append", index=False)
    conn.commit()
    c.close()

    conn.close()